/**
 * @Description
 * @Author LZL
 * @Date 2025/2/25
 */


package com.kcb.connectDB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class connectDB {

    public Connection connectToDatabase() {
        Connection connection = null;
        try {
            // JDBC URL for MySQL
            String url = "jdbc:mysql://localhost:3306/schoolDB";
            String user = "root";
            String password = "root";

            // Establish the connection
            connection = DriverManager.getConnection(url, user, password);
            System.out.println("Connected to the database successfully.");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return connection;
    }

    public boolean addUser(String username, String password) {
        Connection connection = connectToDatabase();
        if (connection == null) {
            return false;
        }

        // Check if the username already exists
        String checkUserQuery = "SELECT * FROM users WHERE username = ?";
        try (PreparedStatement checkStatement = connection.prepareStatement(checkUserQuery)) {
            checkStatement.setString(1, username);
            ResultSet resultSet = checkStatement.executeQuery();
            if (resultSet.next()) {
                System.out.println("Username already exists.");
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

        String insertUserQuery = "INSERT INTO users (username, password) VALUES (?, ?)";
        // 修改表名以确保其有效性
        String createTableQuery = "CREATE TABLE user_" + username + " (course_name VARCHAR(255), teacher_name VARCHAR(255), time VARCHAR(255), location VARCHAR(255))";

        try (Statement statement = connection.createStatement()) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(insertUserQuery)) {
                preparedStatement.setString(1, username);
                preparedStatement.setString(2, password);
                preparedStatement.executeUpdate();
            }
            statement.executeUpdate(createTableQuery);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public boolean addCourse(String username, String courseName, String teacherName, String time, String location) {
        Connection connection = connectToDatabase();
        if (connection == null) {
            return false;
        }

        String insertCourseQuery = "INSERT INTO user_" + username + " (course_name, teacher_name, time, location) VALUES (?, ?, ?, ?)";
        try (PreparedStatement preparedStatement = connection.prepareStatement(insertCourseQuery)) {
            preparedStatement.setString(1, courseName);
            preparedStatement.setString(2, teacherName);
            preparedStatement.setString(3, time);
            preparedStatement.setString(4, location);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public ResultSet inquireCourse(String username, String courseName) {
        Connection connection = connectToDatabase();
        if (connection == null) {
            return null;
        }

        String inquireCourseQuery = "SELECT * FROM user_" + username + " WHERE course_name = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(inquireCourseQuery)) {
            preparedStatement.setString(1, courseName);
            return preparedStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
        }
    }

    public boolean deleteCourse(String username, String courseName) {
        Connection connection = connectToDatabase();
        if (connection == null) {
            return false;
        }

        String deleteCourseQuery = "DELETE FROM user_" + username + " WHERE course_name = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(deleteCourseQuery)) {
            preparedStatement.setString(1, courseName);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public boolean deleteUser(String username, String password) {
        Connection connection = connectToDatabase();
        if (connection == null) {
            return false;
        }

        // Validate the user's credentials
        String validateQuery = "SELECT * FROM users WHERE username = ? AND password = ?";
        try (PreparedStatement validateStatement = connection.prepareStatement(validateQuery)) {
            validateStatement.setString(1, username);
            validateStatement.setString(2, password);
            ResultSet resultSet = validateStatement.executeQuery();
            if (!resultSet.next()) {
                System.out.println("Invalid username or password.");
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

        String deleteUserQuery = "DELETE FROM users WHERE username = ?";
        String dropTableQuery = "DROP TABLE IF EXISTS user_" + username;

        try (Statement statement = connection.createStatement()) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(deleteUserQuery)) {
                preparedStatement.setString(1, username);
                preparedStatement.executeUpdate();
            }
            statement.executeUpdate(dropTableQuery);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public boolean validateLogin(String username, String password) {
        Connection connection = connectToDatabase();
        if (connection == null) {
            return false;
        }

        String query = "SELECT * FROM users WHERE username = ? AND password = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);
            ResultSet resultSet = preparedStatement.executeQuery();
            return resultSet.next();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public boolean updateCourse(String username, String courseName, String teacherName, String time, String location) {
        Connection connection = connectToDatabase();
        if (connection == null) {
            return false;
        }

        String updateCourseQuery = "UPDATE user_" + username + " SET teacher_name = ?, time = ?, location = ? WHERE course_name = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(updateCourseQuery)) {
            preparedStatement.setString(1, teacherName);
            preparedStatement.setString(2, time);
            preparedStatement.setString(3, location);
            preparedStatement.setString(4, courseName);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public boolean courseExists(String username, String courseName) {
        Connection connection = connectToDatabase();
        if (connection == null) {
            return false;
        }

        String inquireCourseQuery = "SELECT * FROM user_" + username + " WHERE course_name = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(inquireCourseQuery)) {
            preparedStatement.setString(1, courseName);
            ResultSet resultSet = preparedStatement.executeQuery();
            return resultSet.next();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public List<String[]> getSortedCoursesByDayOfWeek(String username) {
        Connection connection = connectToDatabase();
        if (connection == null) {
            return null;
        }

        String query = "SELECT course_name, teacher_name, time, location FROM user_" + username + " ORDER BY CASE " +
                   "WHEN SUBSTRING_INDEX(time, ' ', 1) = '周一' THEN 1 " +
                   "WHEN SUBSTRING_INDEX(time, ' ', 1) = '周二' THEN 2 " +
                   "WHEN SUBSTRING_INDEX(time, ' ', 1) = '周三' THEN 3 " +
                   "WHEN SUBSTRING_INDEX(time, ' ', 1) = '周四' THEN 4 " +
                   "WHEN SUBSTRING_INDEX(time, ' ', 1) = '周五' THEN 5 " +
                   "WHEN SUBSTRING_INDEX(time, ' ', 1) = '周六' THEN 6 " +
                   "WHEN SUBSTRING_INDEX(time, ' ', 1) = '周日' THEN 7 " +
                   "ELSE 8 END, time";
        try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
            ResultSet resultSet = preparedStatement.executeQuery();
            List<String[]> courses = new ArrayList<>();
            while (resultSet.next()) {
                String courseName = resultSet.getString("course_name");
                String teacherName = resultSet.getString("teacher_name");
                String time = resultSet.getString("time");
                String location = resultSet.getString("location");
                courses.add(new String[]{courseName, teacherName, time, location});
            }
            return courses;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}
